package com.apes.scm.account.account.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Objects;

/**
 * @description: 商品库存手写sql
 * @author: wsj
 * @time: 2020/11/30 16:21
 */
@Component
public class ProductStockDao extends ApesDao {
    /**
     * 批量查询安全库存数
     * @param param
     * @return
     */
    public JSONArray queryStockSafetyQty(JSONObject param) {
        JSONObject request = new JSONObject();
        String sql = " SELECT A.ID,A.PRODUCT_ID,A.LOCATION_ID,B.BRAND_ID,B.CLASSIFY_ID,B.NAME PRODUCT_NAME,\n" +
                "       C.NAME LOCATION_NAME,A.SAFETY_STOCK_QTY,A.THE_MAX_QTY,A.THE_MIN_QTY,\n" +
                "       (SELECT NAME FROM MAS_CLASSIFY WHERE B.CLASSIFY_ID = ID)CLASSIFY_NAME,\n" +
                "       (SELECT NAME FROM MAS_BRAND WHERE B.BRAND_ID = ID)BRAND_NAME\n" +
                " FROM  ACC_STOCK A,MAS_PRODUCT B,MAS_LOCATION C\n" +
                " WHERE A.PRODUCT_ID = B.ID AND A.LOCATION_ID = C.ID  "; // AND F_GET_DIRECT_SUPPLY_RANGE(B.CLASSIFY_ID, B.BRAND_ID) = 1
        if (Objects.nonNull(param.get("classify"))) {
            request.put("CLASSIFY", param.getString("classify"));
            sql += " AND B.CLASSIFY_ID LIKE '%'|| :CLASSIFY ||'%' ";
        }
        if (Objects.nonNull(param.get("product"))) {
            JSONObject product = param.getJSONObject("product");
            request.put("PRODUCT", product.getString("id"));
            sql += " AND A.PRODUCT_ID =:PRODUCT ";
        }
        if (Objects.nonNull(param.get("location")) && param.getJSONArray("location").size() >0) {
            JSONArray array = param.getJSONArray("location");
            sql += " AND A.LOCATION_ID IN ( ";
            sql = getQueryString(sql, array);
        }
        if (Objects.nonNull(param.get("productGroup")) && param.getJSONArray("productGroup").size() >0) {
            JSONArray array = param.getJSONArray("productGroup");
            sql += " AND B.PRODUCT_GROUP_ID IN ( ";
            sql = getQueryString(sql, array);
        }
        if (Objects.nonNull(param.get("category")) && param.getJSONArray("category").size() >0) {
            JSONArray array = param.getJSONArray("category");
            sql += " AND B.CATEGORY_ID IN ( ";
            sql = getQueryString(sql, array);
        }
        if (param.getBoolean("greater")) {
            sql += " AND A.SAFETY_STOCK_QTY > 0 ";
        }
        if (Objects.nonNull(param.get("brand"))) {
            sql += " AND B.BRAND_ID IN ( '" + param.getJSONObject("brand").getString("id") + "')";
        }
        if (Objects.nonNull(param.get("company"))) {
            sql += " AND A.COMPANY_ID IN ('" + param.getJSONObject("company").getString("id") + "')";
        }
        return toJSONArray(find(sql, request));
    }

    /**
     * 获取查选条件字符串
     * @param sql sql 语句
     * @param array 条件数组
     * @return 字符串
     */
    private String getQueryString(String sql, JSONArray array) {
        StringBuilder sqlBuilder = new StringBuilder(sql);
        for (int i = 0; i < array.size(); i++) {
            JSONObject json = array.getJSONObject(i);
            if (i == 0) {
                sqlBuilder.append("'").append(json.getString("id")).append("'");
            } else {
                sqlBuilder.append(",'").append(json.getString("id")).append("'");
            }
        }
        sqlBuilder.append(")");
        sql = sqlBuilder.toString();
        return sql;
    }

    /**
     * 获取商品的所有库存
     * @param param
     * @return
     */
    public JSONArray getAllStockByProduct(JSONObject param) {
        String sSql = "SELECT \n" +
                "    SUM( (CASE WHEN (A.USABLE_QTY - A.FROZEN_QTY) <= 0 THEN 0 ELSE (A.USABLE_QTY - A.FROZEN_QTY) END)) STOCK_QTY\n" +
                "FROM ACC_STOCK A\n" +
                "WHERE A.PRODUCT_ID IN :PRODUCT_ID";
        return toJSONArray(find(sSql, param));
    }
}
